package com.efast.dmom.mgmt.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 解析Excel文件，测试可用，注意poi的版本是3.15以上4.2以下
 * 
 * @author Wj
 */
public class ExcelUploadUtil {
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param in
	 *            File
	 * @param file
	 *            File
	 * @return Workbook
	 */
	public static Workbook getWorkbok(InputStream in, File file) throws IOException {
		Workbook wb = null;
		// Excel 2003
		if (file.getName().endsWith(EXCEL_XLS)) {
			wb = new HSSFWorkbook(in);
			// Excel 2007/2010
		} else if (file.getName().endsWith(EXCEL_XLSX)) {
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 判断文件是否是excel
	 */
	public static void checkExcelVaild(File file) throws Exception {
		if (!file.exists()) {
			throw new Exception("文件不存在");
		}
		if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
			throw new Exception("文件不是Excel");
		}
	}

	/**
	 * 读取Excel测试，兼容 Excel 2003/2007/2010
	 */
	public static void main(String[] args) {
		try {
			// 同时支持Excel 2003、2007
			// 创建文件对象
			File excelFile = new File("C:\\Users\\Administrator\\Downloads\\远期资源导入模板.xlsx");
			// 文件流
			FileInputStream in = new FileInputStream(excelFile);
			checkExcelVaild(excelFile);
			Workbook workbook = getWorkbok(in, excelFile);
			// 这种方式 Excel2003/2007/2010都是可以处理的
			// Workbook workbook = WorkbookFactory.create(is);

			// Sheet的数量
			// 设置当前excel中sheet的下标：0开始
			int sheetCount = workbook.getNumberOfSheets();

			// 遍历第一个Sheet
			Sheet sheet = workbook.getSheetAt(0);

			// 遍历第三个Sheet
			// Sheet sheet = workbook.getSheetAt(2);

			System.out.println("获取总行数==>" + sheet.getLastRowNum());
			System.out.println();

			// 为跳过第一行目录设置count
			int count = 0;
			for (Row row : sheet) {
				try {
//					// 跳过第一和第二行的目录
					if (count < 2) {
						count++;
						continue;
					}

					// 如果当前行没有数据，跳出循环
					// if ("".equals(row.getCell(0).toString())) {
					// System.out.print("null" + "\t");
					// }

					// 获取总列数(空格的不计算)
					System.out.println("总列数：" + row.getPhysicalNumberOfCells());

					System.out.println("最大列数：" + row.getLastCellNum());

					// for循环的，不扫描空格的列
					// for (Cell cell : row) {
					// System.out.println(cell);
					// }

					int end = row.getLastCellNum();
					for (int i = 0; i < end; i++) {
						Cell cell = row.getCell(i);
						if (cell == null) {
							System.out.print("null" + "\t");
							continue;
						}

						Object obj = getValue(cell);
						if ("".equals(obj)) {
							System.out.print("null" + "\t");
						} else {
							System.out.print(obj + "\t");
						}
					}
					System.out.println();
					System.out.println();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Object getValue(Cell cell) {
		Object obj = null;
		switch (cell.getCellTypeEnum()) {
		case BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case ERROR:
			obj = cell.getErrorCellValue();
			break;
		case NUMERIC:
			DataFormatter dataFormatter = new DataFormatter();
			obj = dataFormatter.formatCellValue(cell);
//			obj = cell.getNumericCellValue();
			// 判断单元格是否属于日期格式
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// java.util.Date类型
				obj = cell.getDateCellValue();
			}
			short dataFormat = cell.getCellStyle().getDataFormat();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			if (dataFormat != 0) {
				// format == 176时是当指定单元格格式为日期是都是176
				if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58 || dataFormat == 176) {
					// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				} else if (dataFormat == 20 || dataFormat == 32) {
					// 时间
					sdf = new SimpleDateFormat("HH:mm");
				}
//				double value = cell.getNumericCellValue();
//				Date date = DateUtil.getJavaDate(value);
//				obj = sdf.format(date);
			}
			break;
		case STRING:
			obj = cell.getStringCellValue();
			break;
		default:
			break;
		}
		return obj;
	}
	
	/**
	 * 获取合并单元格的内容
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public Object getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getValue(fCell) ;
                }
            }
        }
        return null ;
    }
}
